import pandas as pd
import pymysql
from googletrans import Translator
import logging
from datetime import datetime

# 配置日志记录
logging.basicConfig(
    filename='import_errors.log',
    level=logging.ERROR,
    format='%(asctime)s - %(levelname)s - %(message)s'
)


class CSVToMySQL:
    def __init__(self, csv_path, mysql_config, table_name):
        self.csv_path = csv_path
        self.mysql_config = mysql_config
        self.table_name = table_name
        self.error_records = []
        self.translator = Translator()

    def translate_columns(self, df):
        """中文列名翻译为英文"""
        new_columns = []
        for col in df.columns:
            try:
                translated = self.translator.translate(col, src='zh-cn', dest='en').text
                new_columns.append(translated.lower().replace(' ', '_'))
            except Exception as e:
                logging.error(f"列名翻译失败: {col}, 错误: {str(e)}")
                new_columns.append(col)
        df.columns = new_columns
        return df

    def check_table_exists(self, conn):
        """检查表是否存在"""
        with conn.cursor() as cursor:
            cursor.execute(f"SHOW TABLES LIKE '{self.table_name}'")
            return cursor.fetchone() is not None

    def create_table(self, conn, df):
        """自动创建表"""
        create_table_sql = f"CREATE TABLE {self.table_name} ("
        for col in df.columns:
            dtype = 'VARCHAR(255)'  # 默认类型
            if pd.api.types.is_numeric_dtype(df[col]):
                dtype = 'FLOAT'
            elif pd.api.types.is_datetime64_any_dtype(df[col]):
                dtype = 'DATETIME'
            create_table_sql += f"{col} {dtype}, "
        create_table_sql = create_table_sql.rstrip(', ') + ")"

        with conn.cursor() as cursor:
            cursor.execute(create_table_sql)
        conn.commit()

    def process_row(self, conn, row):
        """处理单行数据"""
        try:
            cols = ', '.join(row.index)
            placeholders = ', '.join(['%s'] * len(row))
            sql = f"INSERT INTO {self.table_name} ({cols}) VALUES ({placeholders})"

            with conn.cursor() as cursor:
                cursor.execute(sql, tuple(row))
            conn.commit()
            return True
        except Exception as e:
            error_msg = f"行数据导入失败: {row.to_dict()}, 错误: {str(e)}"
            logging.error(error_msg)
            self.error_records.append({
                'timestamp': datetime.now(),
                'data': row.to_dict(),
                'error': str(e)
            })
            return False

    def export_errors_to_csv(self):
        """导出错误记录到CSV"""
        if self.error_records:
            error_df = pd.DataFrame(self.error_records)
            error_df.to_csv('import_errors.csv', index=False)

    def execute(self):
        """执行导入流程"""
        try:
            # 读取CSV
            df = pd.read_csv(self.csv_path)
            df = df.where(pd.notnull(df), None)

            # 翻译列名
            df = self.translate_columns(df)

            # 连接MySQL
            conn = pymysql.connect(**self.mysql_config)

            # 检查并创建表
            if not self.check_table_exists(conn):
                self.create_table(conn, df)

            # 逐行导入
            for _, row in df.iterrows():
                self.process_row(conn, row)

            # 导出错误记录
            self.export_errors_to_csv()

            return True
        except Exception as e:
            logging.error(f"导入过程发生严重错误: {str(e)}")
            return False
        finally:
            if 'conn' in locals() and conn:
                conn.close()


# 使用示例
if __name__ == "__main__":
    config = {
        'host': '127.0.0.1',
        'user': 'root',
        'password': '123456',
        'database': 'contacts_data',
        'charset': 'utf8mb4'
    }
    importer = CSVToMySQL('import.csv', config, 'user_info')
    if importer.execute():
        print("导入完成，错误记录已保存到import_errors.csv")
    else:
        print("导入过程中发生错误，请查看日志文件")
